package cn.demomaster.quickdb;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.text.TextUtils;
import android.util.Log;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.demomaster.quickdb.mapper.CursorMapper;
import cn.demomaster.quickdb.mapper.MapperManager;
import cn.demomaster.quickdb.model.ColumnInfo;
import cn.demomaster.quickdb.model.TableInfo;
import cn.demomaster.quickdb.utils.DatabaseUtils;

public class TableHelper {
    static QuickDb mQuickDb;

    public TableHelper(QuickDb quickDb) {
        mQuickDb = quickDb;
    }

    /**
     * 生成表结构更新sql
     *
     * @param clazz
     * @return
     * @throws Exception
     */
    public static <T> void upedateTableSql(SQLiteDatabase db, Class<T> clazz) throws Exception {
        TableInfo tableInfo = getTableInfo(clazz);

        List<ColumnInfo> existingColumns = DatabaseUtils.getExistingColumns(db,tableInfo.getTableName());
        Map<String, ColumnInfo> existingColumnMap = new HashMap<>();
        ColumnInfo primaryColumn_exists = null;
        for (ColumnInfo column : existingColumns) {
            if (column.isPrimaryKey()) {
                primaryColumn_exists = column;
            }
            existingColumnMap.put(column.getColumnName().toLowerCase(), column);
        }
        List<ColumnInfo> entityColumns = tableInfo.getTableColumns();
        Map<String, ColumnInfo> entityColumnMap = new HashMap<>();
        ColumnInfo primaryColumn_entity = null;
        for (ColumnInfo column : entityColumns) {
            entityColumnMap.put(column.getColumnName().toLowerCase(), column);
            if (column.isPrimaryKey()) {
                primaryColumn_entity = column;
            }
        }
        //判断有没有移除的字段
        for (ColumnInfo column : existingColumns) {
            boolean isExist = entityColumnMap.containsKey(column.getColumnName().toLowerCase());
            if (!isExist) {
                Log.e("TABLE", tableInfo.getTableName() + "," + column.getColumnName() + "字段已经弃用");
                DatabaseUtils.removeColumn(db, tableInfo.getTableName(), column.getColumnName());
                upedateTableSql(db, clazz);
                return;
            }
        }
        //判断有没有添加的字段
        for (ColumnInfo column : entityColumns) {
            boolean isExist = existingColumnMap.containsKey(column.getColumnName().toLowerCase());
            if (!isExist) {
                Log.w("TABLE", "存在已新增的字段："+tableInfo.getTableName() + "->" + column.getColumnName());
                DatabaseUtils.addColumn(db, tableInfo.getTableName(), column);
                upedateTableSql(db, clazz);
                return;
            }
        }


        //判断有没有修改的字段,优先处理主键
        if(primaryColumn_entity!=null){
            if(primaryColumn_exists!=null){
                if(!primaryColumn_entity.getColumnName().equalsIgnoreCase(primaryColumn_exists.getColumnName())){
                    ColumnInfo columnInfo = entityColumnMap.get(primaryColumn_exists.getColumnName());
                    DatabaseUtils.alterColumn(db, tableInfo.getTableName(), primaryColumn_exists, columnInfo);
                    upedateTableSql(db, clazz);
                    return;
                }
            }
        }

        for (ColumnInfo existingColumn : existingColumns) {
            for (ColumnInfo entityColumn : entityColumns){
                if(existingColumn.getColumnName().equalsIgnoreCase(entityColumn.getColumnName())){
                    // 检查类型是否不同（忽略大小写）
                    if (!existingColumn.getColumnType().equalsIgnoreCase(entityColumn.getColumnType())) {
                        DatabaseUtils.alterColumn(db, tableInfo.getTableName(), existingColumn, entityColumn);
                    }
                    // 检查主键约束是否不同
                    else if (existingColumn.isPrimaryKey() != entityColumn.isPrimaryKey()) {//处理主键 要先弃用之前的主键字段再添加新的主键
                        DatabaseUtils.alterColumn(db, tableInfo.getTableName(), existingColumn, entityColumn);
                    }
                    // 检查其他约束条件（如 NOT NULL, UNIQUE, DEFAULT 等）
                    else if (!DatabaseUtils.getConstraints(existingColumn).equals(DatabaseUtils.getConstraints(entityColumn))) {
                        DatabaseUtils.alterColumn(db, tableInfo.getTableName(), existingColumn, entityColumn);
                    }
                }
            }
        }
//        if (tableInfo.getTableColumns() != null) {
//            for (ColumnInfo column : tableInfo.getTableColumns()) {
//                upedateTableSql(db, tableInfo.getTableName(), column);
//            }
//        }

    }

    public static String upedateTableSql(SQLiteDatabase db, String tableName, ColumnInfo columnInfo) {
        //判斷字段是否存在
        String sql = null;
        ColumnInfo columnInfo1 = DatabaseUtils.getColumnByNameAndType(db, tableName, columnInfo.getColumnName(), columnInfo.getColumnType());
        if (columnInfo1 == null) {//不存在，添加表字段
            Log.e("TABLE", tableName + "," + columnInfo.getColumnName() + "字段不存在");
            if (!columnInfo.isNotNull()) {//非必填字段，添加字段
                sql = String.format("ALTER TABLE %s ADD COLUMN %s", tableName, columnInfo.getColumnName() + " " + columnInfo.getColumnType() + " " + DatabaseUtils.getConstraints(columnInfo));
            }else {//必填字段，添加默认值
                Log.d("TABLE", tableName + "," + columnInfo.getColumnName() + "字段非必填，添加默认值");
                //TODO 处理必填字段，和已有数据冲突问题

            }
        } else {
            //sqlite不支持全部的sql语句，更改表字段语句无法执行，只能先创建新表，备份数据，删除原表，
            //或者利用可视化工具直接修改，但是他执行的也是上边的过程
            //sql = String.format("alter table %s modify %s",tableName,tableColumn.getSqlString());
            if ((columnInfo1.isPrimaryKey() == columnInfo.isPrimaryKey())) {
                if (columnInfo1.isPrimaryKey()) {//如果目标字段是主键，忽略字段是否为空
                    if ((columnInfo1.isNotNull())) {
                        return null;
                    }
                } else if ((columnInfo1.isNotNull() == columnInfo.isNotNull())) {//如果目标字段不是主键，判断为空属性是否匹配
                    return null;
                }
            }
            System.out.println(tableName + "字段：'" + columnInfo.getColumnName() + "'有变化.isNullable:" + columnInfo1.isNotNull() + "<>" + columnInfo.isNotNull() + " ,isPrimaryKey:" + columnInfo1.isPrimaryKey() + "<>" + columnInfo.isPrimaryKey());

            String tableName_backup = "_" + tableName + "_old_" + System.currentTimeMillis();//备份表名
            String tableSql_old = DatabaseUtils.fetchTableCreationSql(db, tableName);
            Log.d("TABLE", "获取到原表：" + tableName + "，结构体:" + tableSql_old);
            if (TextUtils.isEmpty(tableSql_old)) {
                Log.e("TABLE", "未获取到" + tableName + "表结构");
                return null;
            }

            //String createSql = "CREATE TABLE " + toTable + " AS SELECT * FROM " + fromTable;
            //创建表2并拷贝所有表1的内容
            String createSql1 = String.format("CREATE TABLE %s AS SELECT * FROM %s", tableName_backup, tableName);
            //Log.d("TABLE", "创建表2并拷贝所有表1的内容:" + createSql1);
            createSql1 = String.format("ALTER TABLE %s RENAME TO %s", tableName, tableName_backup);
            db.execSQL(createSql1);
            Log.d("TABLE", "对表重命名:" + createSql1);

                /*String regex = "\\((.*?)\\)";
                Pattern pattern = Pattern.compile(regex);
                Matcher matcher = pattern.matcher(creatNewTableSql);
                while (matcher.find()) {
                    Log.d("TABLE", "提取表字段:" + matcher.group(1));
                }*/

                /*String deleteSql = String.format("DROP TABLE %s", tableName);
                db.execSQL(deleteSql);
                Log.d("TABLE", "删除old表:" + deleteSql);*/

            int startIndex = tableSql_old.indexOf("(") + 1;
            int endIndex = tableSql_old.lastIndexOf(")");
            String creatTableSql_new = null;
            if (endIndex > startIndex) {
                String str2 = tableSql_old.substring(startIndex, endIndex);
                String[] strings = str2.split(",");
                //Log.d("TABLE", "提取表字段:" + str2+","+strings.length+ ","+Arrays.toString(strings));
                StringBuilder stringBuilder = new StringBuilder();
                for (String string : strings) {
                    String s = removeFirstTrim(string);
                    if (!s.startsWith(columnInfo.getColumnName() + ' ')) {
                        stringBuilder.append(s)
                                .append(",");
                    }
                }
                creatTableSql_new = "CREATE TABLE " + tableName + "(" + stringBuilder + " " + columnInfo.getColumnName() + " " + columnInfo.getColumnType() + DatabaseUtils.getConstraints(columnInfo) + ")";
                Log.d("TABLE", "增加字段:" + columnInfo.getColumnName());
            }

            if (!TextUtils.isEmpty(creatTableSql_new)) {
                db.execSQL(creatTableSql_new);
                Log.d("TABLE", "创建新表:" + creatTableSql_new);

                String insertSql = "INSERT OR IGNORE INTO " + tableName + " SELECT * FROM " + tableName_backup;
                db.execSQL(insertSql);
                Log.d("TABLE", "复制数据到新表:" + insertSql);
                String deleteSql1 = String.format("DROP TABLE %s", tableName_backup);
                db.execSQL(deleteSql1);
                Log.d("TABLE", "删除临时表:" + deleteSql1);
                //db.execSQL(String.format("ALTER TABLE %s RENAME TO %s",tableName,tableName2));
                //String table_sql2 = generateTableSql(clazz);
                //db.execSQL(table_sql2);
            }
        }
        if (TextUtils.isEmpty(sql)) {
            return null;
        }

        Log.d("TABLE", "创建字段sql:" + sql);
        try {
            db.execSQL(sql);
        } catch (
                SQLiteException e) {
            if (e.getMessage().startsWith("Cannot add a NOT NULL column with default value NULL")) {
                Log.e("TABLE", "字段内容不能为空，和已有数据冲突 无法修改");
            }
        }
        return sql;
    }

    private static String removeFirstTrim(String s) {
        int len = s.length();
        int st = 0;

        if ((st < len) && (s.charAt(st) <= ' ')) {
            return removeFirstTrim(s.substring(st + 1, len));
        }
        return s;
    }


    /**
     * 生成条件查询语句
     *
     * @param tableInfo
     * @param <T>
     * @return
     */


    public static <T> TableInfo getTableInfo(Class<T> clzz) {
        String tableName = MapperManager.getInstance().getTableNameByClass(clzz);// dbTable.name();
        TableInfo tableInfo = new TableInfo();
        tableInfo.setTableName(tableName);
        tableInfo.setColumnInfos(MapperManager.getInstance().getMapper(clzz).getColumnInfoList());
        return tableInfo;
    }

    public static <T> List<T> generateModels(String tableName, String[] params, String whereParams, Class<T> clazz, boolean isArray) {
        Cursor cursor = mQuickDb.getDb().query(tableName, params, whereParams, null, null, null, null);
        return generateModelsByCursorForMapper(cursor, clazz, isArray);
        //return generateModelsByCursor(cursor, clazz, isArray);
    }

    public static <T> List<T> generateModels2(String sql, Class<T> clazz, boolean isArray) {
        TableInfo tableInfo = getTableInfo(clazz);
        Cursor cursor = mQuickDb.execQuerySQL(tableInfo.getTableName(), sql);
        return generateModelsByCursorForMapper(cursor, clazz, isArray);
        //return generateModelsByCursor(cursor, clazz, isArray);
    }

    public static <T> List<T> generateModelsByCursorForMapper(Cursor cursor, Class<T> clazz, boolean isArray) {
        CursorMapper cursorMapper = MapperManager.getInstance().getMapper(clazz);
        return cursorMapper.mapToList(cursor);
    }

    /**
     * 生成查询语句
     */
    public String generatQueryString(String tableName, Map<String, Object> paramsMap) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT * FROM ")
                .append(tableName)
                .append(" WHERE ");
        boolean b = true;
        for (Map.Entry entry : paramsMap.entrySet()) {
            Object valueObj = entry.getValue();
            String valueStr = null;
            if (valueObj instanceof Number) {
                valueStr = valueObj + "";
            } else if (valueObj instanceof String) {
                valueStr = "\"" + valueObj + "\"";
            }
            if (!TextUtils.isEmpty(valueStr)) {
                stringBuilder.append((b ? " " : " and "))
                        .append(entry.getKey())
                        .append("=")
                        .append(valueStr);
                b = false;
            }
        }

        return (!b) ? stringBuilder.toString() : null;
    }
}